﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Inventory
{
    public class ConTransManager:ConnectionManager
    {
        #region Others

        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCONTRN WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Adding/Updating ConsignmentTransfer
        public void AddConsignmentTransfer(ConTransUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OCONTRN (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, Destination, StoreCode, CardCode, CardName, ContactPerson, PaymentTerms, Reference, CustRefPO, BillTo, ShipTo, SalesMan, Remarks, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, WithHoldingTax, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount, Division, DocumentCode, DivReference, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                                        " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @Destination, @StoreCode, @CardCode, @CardName, @ContactPerson, @PaymentTerms, @Reference, @CustRefPO, @BillTo, @ShipTo, @SalesMan, @Remarks, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @WithHoldingTax, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount, @Division, @DocumentCode, @DivReference, GetDate(), @CreatedBy, GetDate(), @ModifiedBy)";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));
            
            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@Destination", oUnit.Destination));

            oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
            oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
            oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
            oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
            oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
            oCommand.Parameters.Add(new SqlParameter("@DocumentCode", oUnit.DocumentCode));
            oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

            oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));



            oCommand.ExecuteNonQuery();

            #region Line Items
            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO CONTRN1 (DocNum, ItemCode, ItemDesc, UoM, QtyOrder, CQtyOrder, Qty, CQty, PriceListCode, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuID) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @QtyOrder, @CQtyOrder, @Qty, @CQty, @PriceListCode, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuID)";



                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));

                    if (oRow["QtyOrder"].ToString() == "")
                    {
                        oRow["QtyOrder"] = "0";
                    }
                    oCommand.Parameters.Add(new SqlParameter("@QtyOrder", Convert.ToDouble(oRow["QtyOrder"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQtyOrder", Convert.ToDouble(oRow["CQtyOrder"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));

                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }

                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuID", oRow["ItemGuID"].ToString()));

                    oCommand.ExecuteNonQuery();
                }

            }
            #endregion

            //_table.Columns.Add("ItemCode", typeof(string));
            //_table.Columns.Add("ItemName", typeof(string));
            //_table.Columns.Add("QtySrvd", typeof(string));
            //_table.Columns.Add("UoM", typeof(string));
            //_table.Columns.Add("LotNumber", typeof(string));

            #region Inventory Issued Items
            foreach (DataRow oRow in oUnit.InventoryIssuedItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO CONTRN2 (DocNum,WhsCode, ItemCode, ItemName, QtySrvd, UnitCost, UoM, LotNumber, IsFree, PriceListCode, ItemGuID) VALUES  (@DocNum, @WhsCode, @ItemCode, @ItemName, @QtySrvd,  @UnitCost, @UoM, @LotNumber, @IsFree, @PriceListCode, @ItemGuID)";

                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@WhsCode", oUnit.Location));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"])));
                    oCommand.Parameters.Add(new SqlParameter("@QtySrvd", Convert.ToDouble(oRow["QtySrvd"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", (bool)oRow["IsFree"]));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuID", oRow["ItemGuID"].ToString()));


                    oCommand.ExecuteNonQuery();
                }
            }




            #endregion

            //#region Accounting Entries
            //oCommand = new SqlCommand();

            //oCommand.Connection = this.Connection;
            //oCommand.CommandType = CommandType.StoredProcedure;
            //oCommand.CommandText = "SP_POST_SALESConsignmentTransfer_ENTRIES";
            //oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));

            //oCommand.ExecuteNonQuery();
            //#endregion

        }

        public void UpdateOrder(ConTransUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OCONTRN SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, Destination=@Destination, StoreCode=@StoreCode, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, PaymentTerms=@PaymentTerms, Reference=@Reference, CustRefPO=@CustRefPO, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, WithHoldingTax=@WithHoldingTax, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, DivReference=@DivReference, DocumentCode=@DocumentCode, Division=@Division, ModifiedBy=@ModifiedBy, DateModified=GetDate()" +
                                                        " WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@Destination", oUnit.Destination));
            oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
            oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));
            
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
            oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
            oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
            oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
            oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
            oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
            oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
            oCommand.Parameters.Add(new SqlParameter("@DocumentCode", oUnit.DocumentCode));
            oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE CONTRN1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            #region Line Items
            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {

                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO CONTRN1 (DocNum, ItemCode, ItemDesc, UoM, QtyOrder, CQtyOrder, Qty, CQty, PriceListCode, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                "(@DocNum, @ItemCode, @ItemDesc, @UoM, @QtyOrder, @CQtyOrder, @Qty, @CQty, @PriceListCode, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";

                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    if (oRow["Qty"] == DBNull.Value)
                    {
                        int _ZeroValue = 0;
                        oRow["Qty"] = _ZeroValue;
                    }
                    oCommand.Parameters.Add(new SqlParameter("@QtyOrder", Convert.ToDouble(oRow["QtyOrder"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQtyOrder", Convert.ToDouble(oRow["CQtyOrder"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                    if (oRow["IsFree"] != DBNull.Value)
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                    }
                    else
                    {
                        oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                    }
                    oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                    oCommand.ExecuteNonQuery();

                }

            }

            #endregion

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE CONTRN2 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            #region Inventory Issued Items
            foreach (DataRow oRow in oUnit.InventoryIssuedItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO CONTRN2 (DocNum,WhsCode, ItemCode, ItemName, QtySrvd, UnitCost, UoM, LotNumber, PriceListCode, ItemGuid) VALUES  (@DocNum, @WhsCode, @ItemCode, @ItemName, @QtySrvd, @UnitCost, @UoM, @LotNumber, @PriceListCode, @ItemGuid)";
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@WhsCode", oUnit.Location));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@QtySrvd", Convert.ToDouble(oRow["QtySrvd"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@UnitCost", Convert.ToDouble(oRow["UnitCost"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));


                    oCommand.ExecuteNonQuery();
                }
            }
            #endregion

            //#region Updating Accounting Entries

            //oCommand = new SqlCommand();
            //oCommand.Connection = this.Connection;
            //oCommand.CommandText = "DELETE FROM DLN3 WHERE DOCNUM=@DOCNUM";
            //oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));
            //oCommand.ExecuteNonQuery();

            
            //oCommand = new SqlCommand();

            //oCommand.Connection = this.Connection;
            //oCommand.CommandType = CommandType.StoredProcedure;
            //oCommand.CommandText = "SP_POST_SALESConsignmentTransfer_ENTRIES";
            //oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));

            //oCommand.ExecuteNonQuery();
            
            //#endregion

        }
        #endregion

        #region Post/Cancel/Close ConsignmentTransfer
        public SqlException PostOrder(string _DOCNUMBER, string _WAREHOUSE, string _DESTINATIONWHOUSE, string PostedBy)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONTRN SET DOCSTATUS='Posted', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", PostedBy));
                oCommand.ExecuteNonQuery();

                #region Applying Deduction to Inventory
                string selectSQL = "SELECT * FROM CONTRN2 WHERE DocNum=@DocNum";


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = selectSQL;
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                foreach (DataRow oRow in ds.Tables[0].Rows)
                {
                    StockManager oManager = new StockManager();
                    oManager.Connection = this.Connection;

                    if (oManager.IsLotNumberExists(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                    {
                        oManager.UpdateStocks(_WAREHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), (Convert.ToDouble(oRow["QtySrvd"]) * -1).ToString(), oRow["UnitCost"].ToString());
                    }
                    else
                    {
                        WarehouseManager oWhs = new WarehouseManager();
                        oWhs.ConnectionString = this.ConnectionString;
                        oWhs.Open();
                        DataTable dt = oWhs.GetWarehouse(_WAREHOUSE);
                        oWhs.Close();
                        oManager.AddStocks(_WAREHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemDesc"].ToString(), (Convert.ToDouble(oRow["Qty"]) * -1).ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), oRow["MfrDate"].ToString(), oRow["ExpiryDate"].ToString(), oRow["UnitCost"].ToString(), "N/A", "Consignment Transfer");
                    }
                }
                #endregion


                #region Applying Transfer to Inventory
                string selectSQLTransfer = "SELECT * FROM CONTRN2 WHERE DocNum=@DocNum";


                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = selectSQLTransfer;
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DOCNUMBER));
                SqlDataAdapter oAdapterTransfer = new SqlDataAdapter();
                DataSet dsTransfer = new DataSet();

                oAdapterTransfer.SelectCommand = oCommand;
                oAdapter.Fill(dsTransfer);

                foreach (DataRow oRow in dsTransfer.Tables[0].Rows)
                {
                    StockManager oManager = new StockManager();
                    oManager.Connection = this.Connection;

                    if (oManager.IsLotNumberExists(_DESTINATIONWHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString()))
                    {
                        oManager.UpdateStocks(_DESTINATIONWHOUSE, oRow["ItemCode"].ToString(), oRow["LotNumber"].ToString(), oRow["QtySrvd"].ToString(), oRow["UnitCost"].ToString());
                    }
                    else
                    {
                        WarehouseManager oWhs = new WarehouseManager();
                        oWhs.ConnectionString = this.ConnectionString;
                        oWhs.Open();
                        DataTable dt = oWhs.GetWarehouse(_DESTINATIONWHOUSE);
                        oWhs.Close();
                        oManager.AddStocks(_DESTINATIONWHOUSE, dt.Rows[0]["Dscription"].ToString(), oRow["ItemCode"].ToString(), oRow["ItemName"].ToString(), oRow["QtySrvd"].ToString(), oRow["UoM"].ToString(), oRow["LotNumber"].ToString(), System.DateTime.Now.ToString(), System.DateTime.Now.ToString(), oRow["UnitCost"].ToString(), "N/A", "Consignment Transfer");
                    }
                }
                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException DeliverOrder(string sOrderNo, string DeliveredBy)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONTRN SET DOCSTATUS='Delivered', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", DeliveredBy));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException CancelOrder(string sOrderNo, string CancelledBy)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONTRN SET DOCSTATUS='Canceled', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", CancelledBy));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException CloseOrder(string sOrderNo, string ClosedBy)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONTRN SET DOCSTATUS='Closed', DateModified=GetDate(), ModifiedBy=@ModifiedBy WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", ClosedBy));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Retreiving

        public DataTable GetDistribution(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM DLN3 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetConsignmentTransfers(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 *, NetAmount - AppliedAmount TotalBalance FROM OCONTRN WHERE DocNum LIKE @DOCNUM ORDER BY RecNo DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetConsignmentTransfersBySIDRNo(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 * FROM OCONTRN WHERE DivReference LIKE @DOCNUM ORDER BY DivReference DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetConsignmentTransfersByCustRefPO(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 * FROM OCONTRN WHERE CustRefPO LIKE @DOCNUM ORDER BY CustRefPO DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        
        public DataTable GetConsignmentTransfersByCustomer(string sCustomerName)
        {
            sCustomerName = sCustomerName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 * FROM OCONTRN WHERE CardName LIKE @DOCNUM ORDER BY CardName DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sCustomerName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }


        public DataTable BrowseConsignmentTransfer(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            sDocumentNumber = "%" + sDocumentNumber + "%";

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCONTRN WHERE DocNum LIKE @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataTable dt = ds.Tables[0];

            return dt;
        }


        public DataRow GetConsignmentTransfer(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OCONTRN WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetConsignmentTransferLineItemForReturn(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CONTRN1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        public DataTable GetConsignmentTransferLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CONTRN1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }


        public DataTable GetConsignmentTransferLineItemsForReturn(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_COPY_ConsignmentTransfer_TO_RET WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }


        public DataTable GetIssuedLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM CONTRN2 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }
        #endregion

        #region Report Retreivable

        public DataTable GetConsignmentTransferStatus(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_DR_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion
    }
}

